from urllib.parse import quote

import xlwt
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render

from app01.models import tongji, 总表
from app01.utils.fieldshtml import fields_to_html, fields_to_save
from app01.utils.pagination import Pagination


def contract_summary_list(request):
    contract_summary_list_all = tongji.objects.all().order_by('-结算月份', '费用类别')
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str, dicts = fields_to_html(request.session['info']['employeeid'], tongji)

    # 生成分布HTML代码
    page = Pagination(request, contract_summary_list_all)

    # 生成表格底部的合计栏
    templist = contract_summary_list_all[page.start_recno:page.end_recno]
    sum_row = templist.aggregate(Count('费用类别'), Sum('结算金额'), Sum('奖励金额'), Sum('考核扣款'),
                                 Sum('能源费'), Sum('bggyjhba'), Sum('bggyjhca'), Sum('bggyjhcb'), Sum('bggyjhcc'),
                                 Sum('bggyjhce'), Sum('bggyjhcf1'), Sum('bggyjhcf2'), Sum('bggyjhcg'), Sum('bggyjhch'),
                                 Sum('bggyjhcj'), Sum('bggyjhcs'), Sum('bggyjhcm'), Sum('bggyjhcn'), Sum('bggyjhfa'),
                                 Sum('bggyjhfb'), Sum('bggyjhfc'), Sum('bggyjhfj'), Sum('bggyjhfn'), Sum('bggyjhfp'),
                                 Sum('bggyjhga'), Sum('bggyjhgb'), Sum('bggyjhia'), Sum('厂部及其他'))
    # 将数字转换成千分位表示
    for key, value in sum_row.items():
        sum_row[key] = '{:,}'.format(value)

    context = {
        'sum_row': sum_row,
        'fields': dicts,
        'select': select_str,
        'data_list': page.data_list,  # 要显示的数据记录
        'page_string': page.html(),  # 分页的HTML代码
        'start_recno': page.start_recno,
        'end_recno': page.end_recno,
        'active4': 'active',
        'title': '统计报表'
    }
    return render(request, 'contract_summary_list.html', context)


def contract_summary_fields(request):
    fields_str = request.GET.get('f')
    sel_fields = fields_str.split(',')
    # print(type(sel_fields))
    fields_to_save(request.session['info']['employeeid'], tongji, sel_fields)
    return JsonResponse({'status': True})


def contract_summary_details_list(request):
    ym = request.GET.get('ym')  # 年月 year month
    ec = request.GET.get('ec')  # 费用类别 expense category

    contract_summary_details_list_all = 总表.objects.filter(结算月份=ym, 费用类别=ec)
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str, dicts = fields_to_html(request.session['info']['employeeid'], 总表)

    # 生成分布HTML代码
    # page = Pagination(request, contract_summary_details_list_all)

    # 生成表格底部的合计栏
    templist = contract_summary_details_list_all
    sum_row = templist.aggregate(Count('费用类别'), Sum('结算金额'), Sum('奖励金额'), Sum('考核扣款'),
                                 Sum('能源费'), Sum('bggyjhba'), Sum('bggyjhca'), Sum('bggyjhcb'), Sum('bggyjhcc'),
                                 Sum('bggyjhce'), Sum('bggyjhcf1'), Sum('bggyjhcf2'), Sum('bggyjhcg'), Sum('bggyjhch'),
                                 Sum('bggyjhcj'), Sum('bggyjhcs'), Sum('bggyjhcm'), Sum('bggyjhcn'), Sum('bggyjhfa'),
                                 Sum('bggyjhfb'), Sum('bggyjhfc'), Sum('bggyjhfj'), Sum('bggyjhfn'), Sum('bggyjhfp'),
                                 Sum('bggyjhga'), Sum('bggyjhgb'), Sum('bggyjhia'), Sum('厂部及其他'))
    # print(page.data_list)

    context = {
        'sum_row': sum_row,
        'fields': dicts,
        'select': select_str,
        'data_list': templist,  # 要显示的数据记录
        'name': ym + ec,
        'ym': ym,
        'ec': ec,
        'active4': 'active',
        'title': '统计报表'
    }
    return render(request, 'contract_summary_details_list.html', context)


def contract_summary_details_fields(request):
    fields_str = request.GET.get('f')
    sel_fields = fields_str.split(',')
    # print(type(sel_fields))
    fields_to_save(request.session['info']['employeeid'], 总表, sel_fields)
    return JsonResponse({'status': True})


def export_excel(request):
    # 开始记录
    start_recno = int(request.GET.get('start_recno', 0))
    # 结束记录
    end_recno = int(request.GET.get('end_recno', 10))

    filename = quote('统计汇总表.xls')
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    data_list = tongji.objects.all().order_by('-结算月份', '费用类别')[start_recno:end_recno]
    fields = list(data_list.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, data_list.model._meta.get_field(title).verbose_name)
    row_index = 1
    for row in data_list.values():
        # print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response


def export_excel_details(request):
    # 结算月份
    ym = request.GET.get('ym', '')
    # 费用类别
    ec = request.GET.get('ec', '')

    filename = quote('%s%s-明细.xls' % (ym, ec))
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    data_list = 总表.objects.filter(结算月份=ym, 费用类别=ec)
    fields = list(data_list.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, data_list.model._meta.get_field(title).verbose_name)
    row_index = 1
    for row in data_list.values():
        # print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response
